<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>SELECT | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-syntax-select.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-syntax-select.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-syntax-select.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-syntax-select.html" rel="nofollow" target="_blank">../en/sql-syntax-select.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-spec.html">SQL Language</a></span>
»
<span class="breadcrumb-node">SELECT</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-syntax-describe-table.html">« DESCRIBE TABLE</a>
</span>
<span class="next">
<a href="sql-syntax-show-columns.html">SHOW COLUMNS »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-syntax-select"></a>SELECT<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]</pre>
</div>
<p><span class="strong strong"><strong>Description</strong></span>: Retrieves rows from zero or more tables.</p>
<p>The general execution of <code class="literal">SELECT</code> is as follows:</p>
<div class="olist orderedlist">
<ol class="orderedlist">
<li class="listitem">
All elements in the <code class="literal">FROM</code> list are computed (each element can be base or alias table). Currently <code class="literal">FROM</code> supports exactly one table. Do note however that the table name can be a pattern (see <a class="xref" href="sql-syntax-select.html#sql-syntax-from" title="FROM Clause">FROM Clause</a> below).
</li>
<li class="listitem">
If the <code class="literal">WHERE</code> clause is specified, all rows that do not satisfy the condition are eliminated from the output. (See <a class="xref" href="sql-syntax-select.html#sql-syntax-where" title="WHERE Clause">WHERE Clause</a> below.)
</li>
<li class="listitem">
If the <code class="literal">GROUP BY</code> clause is specified, or if there are aggregate function calls, the output is combined into groups of rows that match on one or more values, and the results of aggregate functions are computed. If the <code class="literal">HAVING</code> clause is present, it eliminates groups that do not satisfy the given condition. (See <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by" title="GROUP BY">GROUP BY Clause</a> and <a class="xref" href="sql-syntax-select.html#sql-syntax-having" title="HAVING">HAVING Clause</a> below.)
</li>
<li class="listitem">
The actual output rows are computed using the <code class="literal">SELECT</code> output expressions for each selected row or row group.
</li>
<li class="listitem">
If the <code class="literal">ORDER BY</code> clause is specified, the returned rows are sorted in the specified order. If <code class="literal">ORDER BY</code> is not given, the rows are returned in whatever order the system finds fastest to produce. (See <a class="xref" href="sql-syntax-select.html#sql-syntax-order-by" title="ORDER BY">ORDER BY Clause</a> below.)
</li>
<li class="listitem">
If the <code class="literal">LIMIT</code> is specified, the <code class="literal">SELECT</code> statement only returns a subset of the result rows. (See <a class="xref" href="sql-syntax-select.html#sql-syntax-limit" title="LIMIT">LIMIT Clause</a> below.)
</li>
</ol>
</div>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-select-list"></a><code class="literal">SELECT</code> List<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p><code class="literal">SELECT</code> list, namely the expressions between <code class="literal">SELECT</code> and <code class="literal">FROM</code>, represent the output rows of the <code class="literal">SELECT</code> statement.</p>
<p>As with a table, every output column of a <code class="literal">SELECT</code> has a name which can be either specified per column through the <code class="literal">AS</code> keyword :</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT 1 + 1 AS result;

    result
---------------
2</pre>
</div>
<p>Note: <code class="literal">AS</code> is an optional keyword however it helps with the readability and in some case ambiguity of the query
which is why it is recommended to specify it.</p>
<p>assigned by Elasticsearch SQL if no name is given:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT 1 + 1;

    1 + 1
--------------
2</pre>
</div>
<p>or if it’s a simple column reference, use its name as the column name:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT emp_no FROM emp LIMIT 1;

    emp_no
---------------
10001</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-select-wildcard"></a>Wildcard<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>To select all the columns in the source, one can use <code class="literal">*</code>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM emp LIMIT 1;

     birth_date     |    emp_no     |  first_name   |    gender     |     hire_date      |   languages   |   last_name   |    salary
--------------------+---------------+---------------+---------------+--------------------+---------------+---------------+---------------
1953-09-02T00:00:00Z|10001          |Georgi         |M              |1986-06-26T00:00:00Z|2              |Facello        |57305</pre>
</div>
<p>which essentially returns all(top-level fields, sub-fields, such as multi-fields are ignored] columns found.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-from"></a>FROM Clause<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">FROM</code> clause specifies one table for the <code class="literal">SELECT</code> and has the following syntax:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">FROM table_name [ [ AS ] alias ]</pre>
</div>
<p>where:</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">table_name</code>
</span>
</dt>
<dd>
Represents the name (optionally qualified) of an existing table, either a concrete or base one (actual index) or alias.
</dd>
</dl>
</div>
<p>If the table name contains special SQL characters (such as <code class="literal">.</code>,<code class="literal">-</code>,<code class="literal">*</code>,etc…​) use double quotes to escape them:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM "emp" LIMIT 1;

     birth_date     |    emp_no     |  first_name   |    gender     |     hire_date      |   languages   |   last_name   |    salary
--------------------+---------------+---------------+---------------+--------------------+---------------+---------------+---------------
1953-09-02T00:00:00Z|10001          |Georgi         |M              |1986-06-26T00:00:00Z|2              |Facello        |57305</pre>
</div>
<p>The name can be a <a class="xref" href="multi-index.html" title="Multiple indices">pattern</a> pointing to multiple indices (likely requiring quoting as mentioned above) with the restriction that <span class="strong strong"><strong>all</strong></span> resolved concrete tables have <span class="strong strong"><strong>exact mapping</strong></span>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT emp_no FROM "e*p" LIMIT 1;

    emp_no
---------------
10001</pre>
</div>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">alias</code>
</span>
</dt>
<dd>
A substitute name for the <code class="literal">FROM</code> item containing the alias. An alias is used for brevity or to eliminate ambiguity. When an alias is provided, it completely hides the actual name of the table and must be used in its place.
</dd>
</dl>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT e.emp_no FROM emp AS e LIMIT 1;

    emp_no
-------------
10001</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-where"></a>WHERE Clause<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The optional <code class="literal">WHERE</code> clause is used to filter rows from the query and has the following syntax:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">WHERE condition</pre>
</div>
<p>where:</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">condition</code>
</span>
</dt>
<dd>
Represents an expression that evaluates to a <code class="literal">boolean</code>. Only the rows that match the condition (to <code class="literal">true</code>) are returned.
</dd>
</dl>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT last_name FROM emp WHERE emp_no = 10001;

   last_name
---------------
Facello</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-group-by"></a>GROUP BY<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">GROUP BY</code> clause is used to divide the results into groups of rows on matching values from the designated columns. It has the following syntax:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">GROUP BY grouping_element [, ...]</pre>
</div>
<p>where:</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">grouping_element</code>
</span>
</dt>
<dd>
Represents an expression on which rows are being grouped <em>on</em>. It can be a column name, alias or ordinal number of a column or an arbitrary expression of column values.
</dd>
</dl>
</div>
<p>A common, group by column name:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g FROM emp GROUP BY gender;

       g
---------------
null
F
M</pre>
</div>
<p>Grouping by output ordinal:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender FROM emp GROUP BY 1;

    gender
---------------
null
F
M</pre>
</div>
<p>Grouping by alias:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g FROM emp GROUP BY g;

       g
---------------
null
F
M</pre>
</div>
<p>And grouping by column expression (typically used along-side an alias):</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT languages + 1 AS l FROM emp GROUP BY l;

       l
---------------
null
2
3
4
5
6</pre>
</div>
<p>Or a mixture of the above:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender g, languages l, COUNT(*) c FROM "emp" GROUP BY g, l ORDER BY languages ASC, gender DESC;

       g       |       l       |       c
---------------+---------------+---------------
M              |null           |7
F              |null           |3
M              |1              |9
F              |1              |4
null           |1              |2
M              |2              |11
F              |2              |5
null           |2              |3
M              |3              |11
F              |3              |6
M              |4              |11
F              |4              |6
null           |4              |1
M              |5              |8
F              |5              |9
null           |5              |4</pre>
</div>
<p>When a <code class="literal">GROUP BY</code> clause is used in a <code class="literal">SELECT</code>, <em>all</em> output expressions must be either aggregate functions or expressions used for grouping or derivatives of (otherwise there would be more than one possible value to return for each ungrouped column).</p>
<p>To wit:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender;

       g       |       c
---------------+---------------
null           |10
F              |33
M              |57</pre>
</div>
<p>Expressions over aggregates used in output:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g, ROUND((MIN(salary) / 100)) AS salary FROM emp GROUP BY gender;

       g       |    salary
---------------+---------------
null           |253
F              |260
M              |259</pre>
</div>
<p>Multiple aggregates used:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g, KURTOSIS(salary) AS k, SKEWNESS(salary) AS s FROM emp GROUP BY gender;

       g       |        k         |         s
---------------+------------------+-------------------
null           |2.2215791166941923|-0.03373126000214023
F              |1.7873117044424276|0.05504995122217512
M              |2.280646181070106 |0.44302407229580243</pre>
</div>
<div class="tip admon">
<div class="icon"></div>
<div class="admon_content">
<p>If custom bucketing is required, it can be achieved with the use of <a class="xref" href="sql-functions-conditional.html#sql-functions-conditional-case" title="CASE"><code class="literal">CASE</code></a>,
as shown <a class="xref" href="sql-functions-conditional.html#sql-functions-conditional-case-groupby-custom-buckets" title="Conditional bucketing">here</a>.</p>
</div>
</div>
<div class="section">
<div class="titlepage"><div><div>
<h4 class="title">
<a id="sql-syntax-group-by-implicit"></a>Implicit Grouping<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h4>
</div></div></div>
<p>When an aggregation is used without an associated <code class="literal">GROUP BY</code>, an <em>implicit grouping</em> is applied, meaning all selected rows are considered to form a single default, or implicit group.
As such, the query emits only a single row (as there is only a single group).</p>
<p>A common example is counting the number of records:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT COUNT(*) AS count FROM emp;

     count
---------------
100</pre>
</div>
<p>Of course, multiple aggregations can be applied:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, COUNT(*) AS count FROM emp;

      min:i    |      max:i    |      avg:d    |     count:l
---------------+---------------+---------------+---------------
25324          |74999          |48248.55       |100</pre>
</div>
</div>

</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-having"></a>HAVING<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">HAVING</code> clause can be used <em>only</em> along aggregate functions (and thus <code class="literal">GROUP BY</code>) to filter what groups are kept or not and has the following syntax:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">HAVING condition</pre>
</div>
<p>where:</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">condition</code>
</span>
</dt>
<dd>
Represents an expression that evaluates to a <code class="literal">boolean</code>. Only groups that match the condition (to <code class="literal">true</code>) are returned.
</dd>
</dl>
</div>
<p>Both <code class="literal">WHERE</code> and <code class="literal">HAVING</code> are used for filtering however there are several significant differences between them:</p>
<div class="olist orderedlist">
<ol class="orderedlist">
<li class="listitem">
<code class="literal">WHERE</code> works on individual <span class="strong strong"><strong>rows</strong></span>, <code class="literal">HAVING</code> works on the <span class="strong strong"><strong>groups</strong></span> created by ``GROUP BY``
</li>
<li class="listitem">
<code class="literal">WHERE</code> is evaluated <span class="strong strong"><strong>before</strong></span> grouping, <code class="literal">HAVING</code> is evaluated <span class="strong strong"><strong>after</strong></span> grouping
</li>
</ol>
</div>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT languages AS l, COUNT(*) AS c FROM emp GROUP BY l HAVING c BETWEEN 15 AND 20;

       l       |       c
---------------+---------------
1              |15
2              |19
3              |17
4              |18</pre>
</div>
<p>Further more, one can use multiple aggregate expressions inside <code class="literal">HAVING</code> even ones that are not used in the output (<code class="literal">SELECT</code>):</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max, MAX(salary) - MIN(salary) AS diff FROM emp GROUP BY languages HAVING diff - max % min &gt; 0 AND AVG(salary) &gt; 30000;

      min      |      max      |     diff
---------------+---------------+---------------
28336          |74999          |46663
25976          |73717          |47741
29175          |73578          |44403
26436          |74970          |48534
27215          |74572          |47357
25324          |66817          |41493</pre>
</div>
<div class="section">
<div class="titlepage"><div><div>
<h4 class="title">
<a id="sql-syntax-having-group-by-implicit"></a>Implicit Grouping<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h4>
</div></div></div>
<p>As indicated above, it is possible to have a <code class="literal">HAVING</code> clause without a <code class="literal">GROUP BY</code>. In this case, the so-called <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by-implicit" title="Implicit Grouping"><em>implicit grouping</em></a> is applied, meaning all selected rows are considered to form a single group and <code class="literal">HAVING</code> can be applied on any of the aggregate functions specified on this group.
As such, the query emits only a single row (as there is only a single group) and <code class="literal">HAVING</code> condition returns either one row (the group) or zero if the condition fails.</p>
<p>In this example, <code class="literal">HAVING</code> matches:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MIN(salary) AS min, MAX(salary) AS max FROM emp HAVING min &gt; 25000;

      min      |      max
---------------+---------------
25324          |74999</pre>
</div>
</div>

</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-order-by"></a>ORDER BY<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">ORDER BY</code> clause is used to sort the results of <code class="literal">SELECT</code> by one or more expressions:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">ORDER BY expression [ ASC | DESC ] [, ...]</pre>
</div>
<p>where:</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
<code class="literal">expression</code>
</span>
</dt>
<dd>
Represents an input column, an output column or an ordinal number of the position (starting from one) of an output column. Additionally, ordering can be done based on the results <em>score</em>.
The direction, if not specified, is by default <code class="literal">ASC</code> (ascending).
Regardless of the ordering specified, null values are ordered last (at the end).
</dd>
</dl>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>When used along-side, <code class="literal">GROUP BY</code> expression can point <em>only</em> to the columns used for grouping or aggregate functions.</p>
</div>
</div>
<p>For example, the following query sorts by an arbitrary input field (<code class="literal">page_count</code>):</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM library ORDER BY page_count DESC LIMIT 5;

     author      |        name        |  page_count   |    release_date
-----------------+--------------------+---------------+--------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00Z
Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00Z
James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00Z</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-order-by-grouping"></a>Order By and Grouping<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>For queries that perform grouping, ordering can be applied either on the grouping columns (by default ascending) or on aggregate functions.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>With <code class="literal">GROUP BY</code>, make sure the ordering targets the resulting group - applying it to individual elements inside the group will have no impact on the results since regardless of the order, values inside the group are aggregated.</p>
</div>
</div>
<p>For example, to order groups simply indicate the grouping key:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender ORDER BY g DESC;

       g       |       c
---------------+---------------
M              |57
F              |33
null           |10</pre>
</div>
<p>Multiple keys can be specified of course:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender g, languages l, COUNT(*) c FROM "emp" GROUP BY g, l ORDER BY languages ASC, gender DESC;

       g       |       l       |       c
---------------+---------------+---------------
M              |null           |7
F              |null           |3
M              |1              |9
F              |1              |4
null           |1              |2
M              |2              |11
F              |2              |5
null           |2              |3
M              |3              |11
F              |3              |6
M              |4              |11
F              |4              |6
null           |4              |1
M              |5              |8
F              |5              |9
null           |5              |4</pre>
</div>
<p>Further more, it is possible to order groups based on aggregations of their values:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT gender AS g, MIN(salary) AS salary FROM emp GROUP BY gender ORDER BY salary DESC;

       g       |    salary
---------------+---------------
F              |25976
M              |25945
null           |25324</pre>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>Ordering by aggregation is possible for up to <span class="strong strong"><strong>10000</strong></span> entries for memory consumption reasons.
In cases where the results pass this threshold, use <a class="xref" href="sql-syntax-select.html#sql-syntax-limit" title="LIMIT"><code class="literal">LIMIT</code></a> to reduce the number
of results.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-order-by-score"></a>Order By Score<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>When doing full-text queries in the <code class="literal">WHERE</code> clause, results can be returned based on their
<a href="https://www.elastic.co/guide/en/elasticsearch/guide/2.x/relevance-intro.html" class="ulink" target="_top">score</a> or <em>relevance</em> to the given query.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>When doing multiple text queries in the <code class="literal">WHERE</code> clause then, their scores will be
combined using the same rules as Elasticsearch’s
<a class="xref" href="query-dsl-bool-query.html" title="Boolean query">bool query</a>.</p>
</div>
</div>
<p>To sort based on the <code class="literal">score</code>, use the special function <code class="literal">SCORE()</code>:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z
1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z</pre>
</div>
<p>Note that you can return <code class="literal">SCORE()</code> by using a full-text search predicate in the <code class="literal">WHERE</code> clause.
This is possible even if <code class="literal">SCORE()</code> is not used for sorting:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY page_count DESC;

    SCORE()    |    author     |       name        |  page_count   |    release_date
---------------+---------------+-------------------+---------------+--------------------
2.2886353      |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00Z
1.6086556      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00Z</pre>
</div>
<p>NOTE:
Trying to return <code class="literal">score</code> from a non full-text query will return the same value for all results, as
all are equally relevant.</p>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-limit"></a>LIMIT<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">LIMIT</code> clause restricts (limits) the number of rows returns using the format:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">LIMIT ( count | ALL )</pre>
</div>
<p>where</p>
<div class="variablelist">
<dl class="variablelist">
<dt>
<span class="term">
count
</span>
</dt>
<dd>
is a positive integer or zero indicating the maximum <span class="strong strong"><strong>possible</strong></span> number of results being returned (as there might be less matches than the limit). If <code class="literal">0</code> is specified, no results are returned.
</dd>
<dt>
<span class="term">
ALL
</span>
</dt>
<dd>
indicates there is no limit and thus all results are being returned.
</dd>
</dl>
</div>
<p>To return</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT first_name, last_name, emp_no FROM emp LIMIT 1;

  first_name   |   last_name   |    emp_no
---------------+---------------+---------------
Georgi         |Facello        |10001</pre>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-syntax-pivot"></a>PIVOT<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/language/syntax/commands/select.asciidoc">edit</a>
</h3>
</div></div></div>
<p>The <code class="literal">PIVOT</code> clause performs a cross tabulation on the results of the query: it aggregates the results and rotates rows into columns. The rotation is done by turning unique values from one column in the expression - the pivoting column - into multiple columns in the output. The column values are aggregations on the remaining columns specified in the expression.</p>
<p>The clause can be broken down in three parts: the aggregation, the <code class="literal">FOR</code>- and the <code class="literal">IN</code>-subclause.</p>
<p>The <code class="literal">aggregation_expr</code> subclause specifies an expression containing an <a class="xref" href="sql-functions-aggs.html" title="Aggregate Functions">aggregation function</a> to be applied on one of the source columns. Only one aggregation can be provided, currently.</p>
<p>The <code class="literal">FOR</code>-subclause specifies the pivoting column: the distinct values of this column will become the candidate set of values to be rotated.</p>
<p>The <code class="literal">IN</code>-subclause defines a filter: the intersection between the set provided here and the candidate set from the <code class="literal">FOR</code>-subclause will be rotated to become the headers of the columns appended to the end result. The filter can not be a subquery, one must provide here literal values, obtained in advance.</p>
<p>The pivoting operation will perform an implicit <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by" title="GROUP BY">GROUP BY</a> on all source columns not specified in the <code class="literal">PIVOT</code> clause, along with the values filtered through the <code class="literal">IN</code>-clause. Consider the following statement:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM test_emp PIVOT (SUM(salary) FOR languages IN (1, 2)) LIMIT 5;

     birth_date      |    emp_no     |  first_name   |    gender     |      hire_date      |   last_name   |       1       |       2
---------------------+---------------+---------------+---------------+---------------------+---------------+---------------+---------------
null                 |10041          |Uri            |F              |1989-11-12 00:00:00.0|Lenart         |56415          |null
null                 |10043          |Yishay         |M              |1990-10-20 00:00:00.0|Tzvieli        |34341          |null
null                 |10044          |Mingsen        |F              |1994-05-21 00:00:00.0|Casley         |39728          |null
1952-04-19 00:00:00.0|10009          |Sumant         |F              |1985-02-18 00:00:00.0|Peac           |66174          |null
1953-01-07 00:00:00.0|10067          |Claudi         |M              |1987-03-04 00:00:00.0|Stavenow       |null           |52044</pre>
</div>
<p>The query execution could logically be broken down in the following steps:</p>
<div class="olist orderedlist">
<ol class="orderedlist">
<li class="listitem">
a <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by" title="GROUP BY">GROUP BY</a> on the column in the <code class="literal">FOR</code>-clause: <code class="literal">languages</code>;
</li>
<li class="listitem">
the resulting values are filtered through the set provided in the <code class="literal">IN</code>-clause;
</li>
<li class="listitem">
the now filtered column is pivoted to form the headers of the two additional columns appended to the result: <code class="literal">1</code> and <code class="literal">2</code>;
</li>
<li class="listitem">
a <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by" title="GROUP BY">GROUP BY</a> on all columns of the source table <code class="literal">test_emp</code>, except <code class="literal">salary</code> (part of the aggregation subclause) and <code class="literal">languages</code> (part of the <code class="literal">FOR</code>-clause);
</li>
<li class="listitem">
the values in these appended columns are the <code class="literal">SUM</code> aggregations of <code class="literal">salary</code>, grouped by the respective language.
</li>
</ol>
</div>
<p>The table-value expression to cross-tabulate can also be the result of a subquery:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('F'));

   languages   |       'F'
---------------+------------------
null           |62140.666666666664
1              |47073.25
2              |50684.4
3              |53660.0
4              |49291.5
5              |46705.555555555555</pre>
</div>
<p>The pivoted columns can be aliased (and quoting is required to accommodate white spaces), with or without a supporting <code class="literal">AS</code> token:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('M' AS "XY", 'F' "XX"));

   languages   |        XY       |        XX
---------------+-----------------+------------------
null           |48396.28571428572|62140.666666666664
1              |49767.22222222222|47073.25
2              |44103.90909090909|50684.4
3              |51741.90909090909|53660.0
4              |47058.90909090909|49291.5
5              |39052.875        |46705.555555555555</pre>
</div>
<p>The resulting cross tabulation can further have the <a class="xref" href="sql-syntax-select.html#sql-syntax-order-by" title="ORDER BY">ORDER BY</a> and <a class="xref" href="sql-syntax-select.html#sql-syntax-limit" title="LIMIT">LIMIT</a> clauses applied:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT * FROM (SELECT languages, gender, salary FROM test_emp) PIVOT (AVG(salary) FOR gender IN ('F')) ORDER BY languages DESC LIMIT 4;
   languages   |       'F'
---------------+------------------
5              |46705.555555555555
4              |49291.5
3              |53660.0
2              |50684.4</pre>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-syntax-describe-table.html">« DESCRIBE TABLE</a>
</span>
<span class="next">
<a href="sql-syntax-show-columns.html">SHOW COLUMNS »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>